package com.neuedu.Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.neuedu.projos.Cate;
import com.neuedu.utils.CloseUtils;
import com.neuedu.utils.GetConnectionUntils;

public class CateDaoImpl implements CateDao {


	@Override
	public int getAllCounts() {
		Connection OneConnection = GetConnectionUntils.getOneConnection();
		String sql ="select count(*) from cate";
		PreparedStatement prepareStatement = null;
		ResultSet rs = null;
		try {
			prepareStatement =OneConnection.prepareStatement(sql);
			rs = prepareStatement.executeQuery();
			if(rs.next())
			{
				 return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			CloseUtils.close(OneConnection, prepareStatement, rs);
		}
		return 0;
	}

	@Override
	public List<Cate> getCateByLimit(int pageNum, int pageSize) {
		Connection OneConnection = GetConnectionUntils.getOneConnection();
		String sql ="select * from cate limit ?,?";
		List<Cate> lists = new ArrayList<>();
		PreparedStatement prepareStatement = null;
		ResultSet rs = null;
		try {
			prepareStatement=OneConnection.prepareStatement(sql);
			prepareStatement.setInt(1, pageNum);
			prepareStatement.setInt(2,pageSize);
			rs =prepareStatement.executeQuery();
			while(rs.next())
			{
				int cId = rs.getInt(1);
				String cName=rs.getString(2);
				int pId = rs.getInt(3);
				Timestamp timestamp =rs.getTimestamp(4);
				Cate cate = new Cate(cId, cName, pId, timestamp);
				lists.add(cate);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			CloseUtils.close(OneConnection, prepareStatement, rs);
		}
		return lists;
	}

	@Override
	public List<Cate> getAllOneCate() {
		Connection OneConnection = GetConnectionUntils.getOneConnection();
		String sql ="select * from cate where pid=0";
		List<Cate> lists = new ArrayList<>();
		PreparedStatement prepareStatement = null;
		ResultSet rs = null;
		try {
			prepareStatement=OneConnection.prepareStatement(sql);
			rs =prepareStatement.executeQuery();
			while(rs.next())
			{
				int cId = rs.getInt(1);
				String cName=rs.getString(2);
				int pId = rs.getInt(3);
				Timestamp timestamp =rs.getTimestamp(4);
				Cate cate = new Cate(cId, cName, pId, timestamp);
				lists.add(cate);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally {
			CloseUtils.close(OneConnection, prepareStatement, rs);
		}
		return lists;
	}
	public static void main(String[] args) {
		List<Cate> cates=new CateDaoImpl().getAllOneCate();
		System.out.println(cates);
	}

	@Override
	public void addCate(Cate cate) {
		Connection OneConnection = GetConnectionUntils.getOneConnection();
		String sql ="insert into cate(cate_name,pid,cate_time) values(?,?,?)";
		PreparedStatement prepareStatement = null;
		try {
			prepareStatement=OneConnection.prepareStatement(sql);
			prepareStatement.setString(1, cate.getcName());
			prepareStatement.setInt(2, cate.getpId());
			prepareStatement.setTimestamp(3, cate.getTimestamp());
			prepareStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
